package com.xz.group12306.business.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.xz.group12306.business.common.DBUtils;
import com.xz.group12306.business.dao.UserDao;
import com.xz.group12306.domain.Cart;
import com.xz.group12306.domain.User;

public class UserDaoImpl extends DBUtils implements UserDao{
	private Connection conn;
	public UserDaoImpl(Connection conn) {
		this.setConn(conn);
	}
	
	
	/**
	 * author  胡晨倩
	 */
	//查询登录
	@Override
	public User selectUser(String username, String password, String rule) {
		// TODO Auto-generated method stub
		Connection conn = null;
		conn=DBUtils.getConnection();
		DBUtils.beginTransaction(conn);
		String sql="select * from tab_user where username=? and password=? and rule=?";
		Object param[]={username,password,rule};
		User user=this.executeOneUser(sql, param);
		DBUtils.closeConnection(conn);
		return user;

	}
	
	
	@Override
	public User login(String username, String password){
		// SQL语句
		StringBuffer buff = new StringBuffer();
		buff.append("SELECT u.*, ");
		buff.append("c.cityid ccityid, c.city ccity, c.father cfather, ");
		buff.append("p.id pid, p.provinceid pprovinceid, p.province pprovince, ");
		buff.append("t.id tid, t.content tcontent, ");
		buff.append("e.id eid, e.content econtent ");
		buff.append("FROM tab_user  u, tab_city c, tab_province p, tab_usertype t, tab_certtype e ");
		buff.append("WHERE u.city = c.cityid AND c.father = p.provinceid AND u.usertype = t.id AND u.cert_type = e.id ");
		buff.append("username=? AND password = ? ");
		String find_sql = buff.toString();
		
		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
			//设置语句对象，SQL语句条件
	    try {
		    pstmt = conn.prepareStatement(find_sql);		
			pstmt.setString(1, username);
			pstmt.setString(2, password);

			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 解析结果集对象，封装查询结果
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setRule(rs.getString("rule"));
				user.setRealname(rs.getString("realname"));
				user.setSex(rs.getString("sex"));

				// city
				/*Province province = new Province();
				province.setId(rs.getInt("pid"));
				province.setProvince(rs.getString("pprovince"));
				province.setProvinceId(rs.getString("pprovinceid"));
				
				City city = new City();
				city.setId(rs.getInt("cid"));
				city.setCityId(rs.getString("ccityid"));
				city.setCity(rs.getString("ccity"));
				city.setProvince(province);*/
				
				user.setCity(rs.getString("city"));

				// CertType
				user.setCertType(rs.getString("cert_type"));

				user.setCert(rs.getString("cert"));
				user.setBirthday(rs.getDate("birthday"));

				// UserType
				user.setUserType(rs.getString("userType"));

				user.setContent(rs.getString("content"));
				/*user.setStatus(rs.getString("status"));
				user.setLoginIp(rs.getString("login_ip"));*/
				//user.setImagePath(rs.getString("image_path"));
				System.out.println(user);
			}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return user;
		
	}
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	
	/**
	 * author  沈姚楠
	 */
	//向数据中添加注册的用户的信息
	@Override
	public int save(User user) throws SQLException {
		// TODO Auto-generated method stub
		Connection conn=DBUtils.getConnection();
		//SQL语句
		String save_sql = "insert into tab_user(username,password,rule,realname,sex,city,cert_Type,cert,birthday,userType,content)"
				+ "values(?,?,2,?,?,2,?,?,?,?,?)";

		PreparedStatement pstmt = null;
		int row = 0;
		int idx = 1;
		try {
			//设置语句对象，SQL语句条件
			pstmt = conn.prepareStatement(save_sql);
			pstmt.setString(idx, user.getUsername());
			pstmt.setString(++idx, user.getPassword());
			//pstmt.setString(++idx, user.getRule());
			pstmt.setString(++idx, user.getRealname());
			pstmt.setString(++idx, user.getSex());
			//pstmt.setString(++idx, user.getCity());
			pstmt.setString(++idx, user.getCertType());
			pstmt.setString(++idx, user.getCert());
			pstmt.setDate(++idx,
					new java.sql.Date(user.getBirthday().getTime()));
			pstmt.setString(++idx, user.getUserType());
			pstmt.setString(++idx, user.getContent());

			row = pstmt.executeUpdate();
		} finally {
			//关闭连接
			DBUtils.closeStatement(null, pstmt);
		}
		return row;
	}
	

	@Override
	public User allUser(User one) throws SQLException {
		// TODO Auto-generated method stub
		StringBuffer sql=new StringBuffer();
		sql.append("select username from tab_user where");
		//查询条件标记
		boolean tag = false;
	
		//查询条件username字段
		String username = one.getUsername();
		if(username!=null && !username.isEmpty()){
			sql.append("username='"+username+"'");
			tag = true;
		}		
		//若没有查询条件则返回对象为null
		if(!tag){
			return null;
		}
		
		User user = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				// 解析结果集对象，封装查询结果
				user = new User();
				user.setUsername(rs.getString("username"));

			}
		} finally {
			DBUtils.closeStatement(rs, pstmt);
	}
		return user;
}

	/**
	 * author  胡晨倩
	 */
	//修改密码
	@Override
	public int updatePwd(String password, int id) {
		// TODO Auto-generated method stub
		conn=DBUtils.getConnection();
		DBUtils.beginTransaction(conn);
		String sql="update tab_user set password=? where id=?";
		Object param[]={password,id};
		int result=this.executeUpdate(sql, param);
		DBUtils.closeConnection(conn);
		return result;
	}

	/**
	 * author  胡晨倩
	 */
	//分页查询所有的用户订单信息
	@Override
	public List<Cart> findCartByUser(User user, int page, int rows) throws SQLException {
		// TODO Auto-generated method stub
		int start=(page-1)*rows;
		List<Cart> carts=new ArrayList<Cart>();
		int userid=user.getId();

		String sql="select  *  from tab_cart where userid='"+userid+"'  limit  "+start+","+rows+"";

		        	System.out.println(sql);

		Cart cart=null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;


		try {
		pstmt=conn.prepareStatement(sql);

		rs = pstmt.executeQuery();
		while(rs.next()) {
		// 解析结果集对象，封装查询结果
		//解析结果集对象，封装查询结果
		cart=new Cart();

		cart.setUserid(rs.getInt("userid"));
		cart.setTicketid(rs.getInt("ticketid"));
		cart.setId(rs.getInt("id"));
		cart.setDepature(rs.getString("despature"));
		cart.setDestinction(rs.getString("destinction"));
		cart.setCostTime(rs.getString("costtime"));
		cart.setStartTime(rs.getDate("starttime"));
        cart.setTrainNo(rs.getString("trainno"));


		carts.add(cart);
		}
		} finally {
		DBUtils.closeStatement(rs, pstmt);
		}
	 return carts;
	}

	/**
	 * author  沈姚楠
	 */
	//修改用户信息
	@Override
	public void updateUser(User user) {
		// TODO Auto-generated method stub
		Connection conn=DBUtils.getConnection();
		String sql="update tab_user set username='"+user.getUsername()+"',content='"+user.getContent()+"' where id='"+user.getId()+"'";
		PreparedStatement pstmt=null;
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.executeUpdate();
			DBUtils.closeConnection(conn);
			System.out.println("修改用户信息成功！");
			System.out.println(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
